﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;



/// <summary>
///db_courseware 的摘要说明
/// </summary>
public class db_courseware
{
    SqlDBClass db;
	public db_courseware()
	{
        db = new SqlDBClass();
	}

    /// <summary>
    /// 查询所有 课件
    /// </summary>
    /// <returns></returns>
    public DataSet selectCoursewareALL() {
        //courseware
        string strsql = "select cw.coursewareId ,cw.about, c.about courseId,cw.uploadTime,cw.coursewareUrl,cw.picUrl,t.teacherName teacherId ,c.courseName,cw.coursewareName from teacher t,course c,courseware cw where cw.courseId=c.courseId and cw.teacherId = t.teacherId and cw.status ='0' order by cw.coursewareId";
        return db.execSql(strsql);
    }


    /// <summary>
    /// 查询所有课件搜索
    /// </summary>
    /// <param name="coursewareId"></param>
    /// <param name="courseId"></param>
    /// <param name="coursewareName"></param>
    /// <param name="teacherId"></param>
    /// <returns></returns>
    public DataSet selectCourseware_search(string coursewareId, string courseId, string coursewareName, string teacherId)
    {
        //courseware
        string strsql = "select cw.coursewareId ,cw.about, c.courseName courseId,cw.uploadTime,cw.coursewareUrl,cw.picUrl,t.teacherName teacherId,cw.coursewareName from teacher t,course c,courseware cw where cw.courseId=c.courseId and cw.teacherId = t.teacherId and cw.status ='0' and c.status ='0' ";
        strsql+="and cw.coursewareId like '%"+coursewareId+"%' ";
        strsql += "and cw.coursewareName like '%" + coursewareName + "%' ";
        if(!courseId.Equals(""))
        {
         strsql+="and cw.courseId='"+courseId+"'" ;        
        }
        if(!teacherId.Equals(""))
        {
         strsql+="and cw.teacherId='"+teacherId+"'" ;        
        }

        strsql += " order by cw.uploadTime";
        return db.execSql(strsql);
    }


    /// <summary> 
    /// 查询单个 课件 
    /// </summary> 
    /// <param name="coursewareId"></param>
    /// <returns></returns> 
    public DataSet selectCoursewareONE(string coursewareId) { //courseware 
    string strsql = "select * from courseware where coursewareId=" + coursewareId; 
    return db.execSql(strsql); }

    /// <summary>
    /// 删除课件
    /// </summary>
    /// <param name="coursewareId"></param>
    /// <returns></returns>
    public Boolean delCourseware(string coursewareId)
    {  
        string strsql = "update courseware set status = '1' where coursewareId=" + coursewareId;
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 增加课件
    /// </summary>
    /// <param name="coursewareId"></param>
    /// <param name="about"></param>
    /// <param name="courseId"></param>
    /// <param name="picUrl"></param>
    /// <param name="teacherId"></param>
    /// <returns></returns>
    public Boolean addCourseware(string coursewareId, string about, string courseId, string coursewareUrl, string picUrl, string teacherId, string coursewareName)
    {
        string[] str = new string[2];
         str[0] = "insert into courseware(coursewareId,about,courseId,uploadTime,coursewareUrl,picUrl,teacherId,status,coursewareName)values('" + coursewareId + "','" + about + "','" + courseId + "', getdate() ,'" + coursewareUrl + "','" + picUrl + "','" + teacherId + "','0','" + coursewareName + "')";
         str[1] = "update course set updateTime=getdate() where courseid='" + courseId + "'";
         return db.execSqlTran(str);
    }

    /// <summary>
    /// 更新课件
    /// </summary>
     /// <param name="coursewareId"></param>
    /// <param name="about"></param>
    /// <param name="courseId"></param>
    /// <param name="picUrl"></param>
    /// <param name="teacherId"></param>
    /// <returns></returns>
    public Boolean updateCourseware(string coursewareId, string about, string courseId, string coursewareUrl, string picUrl, string teacherId, string coursewareName)
    {
        string strsql = "update courseware set about='" + about + "',courseId='" + courseId + "',coursewareUrl='" + coursewareUrl + "',picUrl='" + picUrl + "',teacherId='" + teacherId + "',coursewareName='"+coursewareName+"' where coursewareId=" + coursewareId;
        return db.execSqlNoQuery(strsql);
    }


    /// <summary>
    /// 生产最新的课件ID
    /// </summary>
    /// <returns></returns>
    public string getNewCoursewareId()
    {
        string strsql = "select  coalesce(nullif(max(convert(int,CoursewareId)),''),'0')+1 as coursewareId  from courseware";
        DataSet ds = new DataSet();
        string coursewareId = "";
        ds = db.execSql(strsql);

        if (ds.Tables.Count != 0)
        {
            coursewareId = ds.Tables[0].Rows[0]["coursewareId"].ToString();
        }

        return coursewareId;
    }

    /// <summary>
    /// 根据课程查询该课程课件信息
    /// </summary>
    /// <param name="courseId"></param>
    /// <returns>返回行号，课件名称，课件编号</returns>
    public DataSet selectCoursewareByCourse(string courseId) {
        string strsql = "select ROW_NUMBER() OVER(ORDER BY cw.coursewareid ASC ) as num,cw.coursewareName ,cw.coursewareid from course c,courseware cw where cw.courseid=c.courseid and c.courseid='"+courseId+"'";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 根据课件编号查询课件信息
    /// </summary>
    /// <param name="coursewareId"></param>
    /// <returns></returns>
    public DataSet selectCoursewareByCourseID(string coursewareId)
    {
        //courseware
        string strsql = "select cw.coursewareId ,cw.about, c.courseName,cw.uploadTime,cw.coursewareUrl,cw.picUrl,t.teacherName teacherId,cw.coursewareName,c.courseId from teacher t,course c,courseware cw where cw.courseId=c.courseId and cw.teacherId = t.teacherId and cw.status ='0' and cw.coursewareid='" + coursewareId + "'";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 根据老师ID,查询该改老师拥有的课件数量和课程数量
    /// </summary>
    /// <param name="teacherID"></param>
    /// <returns>课件数量,课程数量</returns>
    public DataSet selectNumByTeacherID(string teacherID)
    {
        string strsql = "select count(distinct cw.coursewareid)  as cwnum,count(distinct c.courseid) as cnum from  course c  left join courseware cw on c.courseid = cw.courseid where c.status ='0' and c.teacherid='" + teacherID + "'";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 学生下载课件搜索
    /// </summary>
    /// <param name="userId"></param>
    /// <param name="coursewareName"></param>
    /// <param name="courseName"></param>
    /// <param name="courseType"></param>
    /// <param name="time"></param>
    /// <returns></returns>
    public DataSet selectUserCoursewareDown_search(string userId, string coursewareName, string CourseId, string courseType, string time)
    {
        string strsql = "select c.courseID,c.courseName,ct.typeName,t.teacherName,cwd.[time],cw.coursewareName,cw.coursewareid from [user] u,course c ,coursetype ct,teacher t,courseware cw,coursewaredown cwd where cwd.userid=u.userid and cwd.coursewareid=cw.coursewareid and cw.courseid=c.courseid and c.teacherid=t.teacherid and c.coursetype=ct.typeid and u.userid='" + userId + "'";
        strsql += "and cw.CoursewareName like '%" + coursewareName + "%' ";
        strsql += "and cw.CourseId like '%" + CourseId + "%' ";
        if (!courseType.Equals(""))
        {
            strsql += "and c.courseType='" + courseType + "' ";
        }
        if (!time.Equals("all"))
        {
            strsql += "and cwd.[time] between dateadd(month,-" + time + ",dateadd(day,-1,getdate())) and getdate()";
        }
        strsql += " order by cwd.[time] desc ";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 学生下载课件次数
    /// </summary>
    /// <param name="stuId"></param>
    /// <returns></returns>
    public DataSet selectCoursewareDownByStuIdCount(string stuId)
    {
        string strsql = "select count(coursewareid) as cwdnum from coursewaredown where userid='" + stuId + "'";
        return db.execSql(strsql);
    }
    
    /// <summary>
    /// 用户下载课件
    /// </summary>
    /// <param name="stuid"></param>
    /// <param name="coursewareid"></param>
    /// <returns></returns>
    public bool coursewareDown(string stuid, string coursewareid)
    {
        string strsql = "insert into coursewaredown(coursewareid,userid,[time]) values('" + coursewareid + "','" + stuid + "',getdate())";
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 统计类型下载的次数
    /// </summary>
    /// <param name="type"></param>
    /// <param name="time_1"></param>
    /// <param name="time_2"></param>
    /// <returns></returns>
    public DataSet statisticsDown(string type, string time_1, string time_2)
    {
        string strsql = "select count(cwd.id) as num from coursewaredown cwd,course c,courseware cw where cw.courseid=c.courseid and cwd.coursewareid=cw.coursewareid and c.coursetype='" + type + "' and cwd.[time] between '" + time_1 + "' and DATEADD(day,1,'" + time_2 + "')  group by c.coursetype";
        return db.execSql(strsql);
    }
}